package cn.sciento.transfer.infra.datasource.sql;

import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Assert;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

public class SqlBuilder {
  private static final Logger LOGGER = LoggerFactory.getLogger(SqlBuilder.class);
  
  public static final String SQL_DML_INSERT = "INSERT INTO %s ( %s ) VALUES ( %s )";
  
  public static final String SQL_DML_UPDATE = "UPDATE %s SET %s WHERE %s";
  
  public static final String SQL_DML_DELETE = "DELETE FROM %s WHERE %s";
  
  public static final String SQL_DML_SELECT_ALL_COLUMN = "SELECT * FROM %s WHERE %s";
  
  public static final String SQL_DML_SELECT_CHECK_EXIST = "SELECT COUNT(*) FROM %s WHERE %s";
  
  public static final String SQL_DDL_RENAME_TABLE = "RENAME TABLE %s TO %s";
  
  public static final String SQL_DDL_DROP_TABLE = "DROP TABLE IF EXISTS %s";
  
  public static final String SQL_WHERE_DEFAULT = " 1 = 1 ";
  
  public static final String SQL_DELIMITOR_EQUAL = " = ";
  
  public static final String SQL_DELIMITOR_AND = " AND ";
  
  public static final String SQL_DELIMITOR_COMMA = " , ";
  
  public static final String SQL_PLACEHOLDER = " ? ";
  
  public static final String SQL_MYSQL_INIT_DDL = "SHOW CREATE TABLE  %s";
  
  public static final String SQL_ORACLE_INIT_DDL = " SELECT to_char(DBMS_METADATA.GET_DDL('TABLE','%s')) AS script FROM DUAL ";
  
  public static final String SELECT_TABLE = "SELECT%n\tsb.name AS table_name,%n\t cast(sep.value as nvarchar(500))   AS table_comment %nFROM%n\tsysobjects sb%n\tLEFT JOIN sys.extended_properties sep ON sb.id = sep.major_id %n\tAND sep.minor_id = 0 %nWHERE%nsb.name = '%s' ";
  
  public static final String SELECT_CLUMNS = "SELECT%n\tsc.NAME AS column_name,%n\tcast( sep.VALUE AS NVARCHAR ) AS column_comment,%n\tst.NAME AS column_type ,%n\tsc.length AS column_size,%n\tsc.isnullable AS is_null_able,%n\tCOLUMNPROPERTY( sc.id,sc.name,'IsIdentity') AS is_identity,%n\tsct.text AS column_default %nFROM%n\tsyscolumns sc%n\tLEFT JOIN syscomments sct ON sc.cdefault = sct.id%n\tLEFT JOIN sys.extended_properties sep ON sc.id = sep.major_id %n\tAND sc.colid = sep.minor_id%n\tLEFT JOIN systypes st ON sc.xusertype = st.xusertype %nWHERE%n\tsc.id = ( SELECT sb.id FROM sysobjects sb WHERE sb.NAME = '%s' AND sb.xtype = 'U' )";
  
  public static final String SELECT_INDEX = "EXEC Sp_helpindex %s";
  
  public static final String SQL_MYSQL_SELECT_PRIMARYKEY = "SELECT%n\ttc.table_schema,%n\ttc.table_name,%n\ttc.constraint_type,%n\ttc.constraint_name,%n\tGROUP_CONCAT(kcu.COLUMN_NAME) as index_column_names%nFROM%n\tinformation_schema.table_constraints tc%n\tJOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on kcu.constraint_name = tc.constraint_name and kcu.table_schema = tc.table_schema and kcu.table_name = tc.table_name%nWHERE%n\ttc.table_name = '%s'  -- 表名#1%n\tAND tc.TABLE_SCHEMA = '%s' -- 数据库名#2%n\tAND tc.constraint_type IN ('PRIMARY KEY', 'UNIQUE')%n\tGROUP BY %n\ttc.table_schema,%n\ttc.table_name,%n\ttc.constraint_type,%n\ttc.constraint_name%n\tORDER BY tc.CONSTRAINT_TYPE;";
  
  public static final String SQL_ORACLE_SELECT_PRIMARYKEY = "SELECT%n\tai.index_name,%n\tuic.column_name%n%nFROM%n\tALL_INDEXES ai LEFT JOIN user_ind_columns uic ON ai.index_name = uic.index_name%nWHERE%nai.uniqueness='UNIQUE' %n\tAND ai.OWNER = '%s'%n\tAND ai.table_name = '%s'";
  
  public static String buildInsert(List<String> insertFieldList, String tableName) {
    Assert.notEmpty(insertFieldList, "field list cannot be null or empty.");
    Assert.notNull(tableName, "table name cannot be null.");
    String insertColumnStr = transformFieldsToColumn(insertFieldList);
    String insertPlaceholderStr = transformFieldsToPlaceholder(insertFieldList);
    return String.format("INSERT INTO %s ( %s ) VALUES ( %s )", new Object[] { tableName, insertColumnStr, insertPlaceholderStr });
  }
  
  public static String buildUpdate(List<String> updateFieldList, List<String> whereFieldList, String tableName) {
    Assert.notEmpty(updateFieldList, "field list cannot be null or empty.");
    Assert.notNull(tableName, "table name cannot be null.");
    String updateColumnWithPlaceholderStr = transformFieldsToColumnWithPlaceholderByDelimitor(updateFieldList, " , ");
    String whereColumnWithPlaceholderStr = " 1 = 1 ";
    if (!CollectionUtils.isEmpty(whereFieldList))
      whereColumnWithPlaceholderStr = transformFieldsToColumnWithPlaceholderByDelimitor(whereFieldList, " AND "); 
    return String.format("UPDATE %s SET %s WHERE %s", new Object[] { tableName, updateColumnWithPlaceholderStr, whereColumnWithPlaceholderStr });
  }
  
  public static String buildDelete(List<String> whereFieldList, String tableName) {
    Assert.notNull(tableName, "table name cannot be null.");
    String whereColumnWithPlaceholderStr = " 1 = 1 ";
    if (!CollectionUtils.isEmpty(whereFieldList))
      whereColumnWithPlaceholderStr = transformFieldsToColumnWithPlaceholderByDelimitor(whereFieldList, " AND "); 
    return String.format("DELETE FROM %s WHERE %s", new Object[] { tableName, whereColumnWithPlaceholderStr });
  }
  
  public static String buildDelete(List<String> whereFieldList, String tableName, String appendingCustomSqlRawSlice) {
    return buildDelete(whereFieldList, tableName) + (String)Optional.<String>ofNullable(appendingCustomSqlRawSlice).orElse("");
  }
  
  public static String buildSelectAllColumn(List<String> whereFieldList, String tableName) {
    Assert.notNull(tableName, "table name cannot be null.");
    String whereColumnWithPlaceholderStr = " 1 = 1 ";
    if (!CollectionUtils.isEmpty(whereFieldList))
      whereColumnWithPlaceholderStr = transformFieldsToColumnWithPlaceholderByDelimitor(whereFieldList, " AND "); 
    return String.format("SELECT * FROM %s WHERE %s", new Object[] { tableName, whereColumnWithPlaceholderStr });
  }
  
  public static String buildSelectAllColumn(List<String> whereFieldList, String tableName, String appendingCustomSqlRawSlice) {
    return buildSelectAllColumn(whereFieldList, tableName) + (String)Optional.<String>ofNullable(appendingCustomSqlRawSlice).orElse("");
  }
  
  public static String buildSelectCheckExists(List<String> whereFieldList, String tableName) {
    Assert.notNull(tableName, "table name cannot be null.");
    String whereColumnWithPlaceholderStr = " 1 = 1 ";
    if (!CollectionUtils.isEmpty(whereFieldList))
      whereColumnWithPlaceholderStr = transformFieldsToColumnWithPlaceholderByDelimitor(whereFieldList, " AND "); 
    return String.format("SELECT COUNT(*) FROM %s WHERE %s", new Object[] { tableName, whereColumnWithPlaceholderStr });
  }
  
  public static String buildRenameTable(String oldTableName, String newTableName) {
    Assert.notNull(oldTableName, "old table name cannot be null.");
    Assert.notNull(newTableName, "new table name cannot be null.");
    return String.format("RENAME TABLE %s TO %s", new Object[] { oldTableName, newTableName });
  }
  
  public static String builderDropTable(String tableName) {
    Assert.isTrue(!StringUtils.isEmpty(tableName), "table name cannot be null");
    return String.format("DROP TABLE IF EXISTS %s", new Object[] { tableName });
  }
  
  private static String transformFieldsToColumn(List<String> fieldList) {
    String[] columnList = (String[])fieldList.parallelStream().map(FieldBuilder::formatFieldToColumn).toArray(x$0 -> new String[x$0]);
    return StringUtils.arrayToCommaDelimitedString((Object[])columnList);
  }
  
  private static String transformFieldsToPlaceholder(List<String> fieldList) {
    List<String> placeholderList = (List<String>)fieldList.parallelStream().map(item -> " ? ").collect(Collectors.toList());
    return StringUtils.arrayToCommaDelimitedString(placeholderList.toArray());
  }
  
  private static String transformFieldsToColumnWithPlaceholderByDelimitor(List<String> fieldList, String delimitorStr) {
    String[] columnWithPlaceholderList = (String[])fieldList.parallelStream().map(item -> FieldBuilder.formatFieldToColumn(item) + " = " + " ? ").toArray(x$0 -> new String[x$0]);
    return StringUtils.arrayToDelimitedString((Object[])columnWithPlaceholderList, delimitorStr);
  }
}
